package com.windfone.dao.impl.jdbc.mysql;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowCallbackHandler;

import com.windfone.common.Constant;
import com.windfone.dao.PromotionsDao;
import com.windfone.model.Fitting;
import com.windfone.model.Promotions;

public class PromotionsDaoImpl implements PromotionsDao {
	private JdbcTemplate jdbcTemplate;

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	String DELETE_SQL = " DELETE FROM  promotions WHERE id=? ";

	public int deletePromotions(final int keyValue) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(DELETE_SQL,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)
								throws SQLException {
							ps.setInt(1, keyValue);
						}
					});
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}

	String GET_ALL_NUMBER_SQL = " SELECT COUNT(1) FROM  promotions  ";

	public int getAllRecorderNumber() {
		final List<Integer> result = new ArrayList<Integer>();
		try {
			jdbcTemplate.query(
				GET_ALL_NUMBER_SQL, 
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						result.add(rs.getInt(1));
					}
				}
			);
		} catch (Exception e) {
			e.printStackTrace();
		}
		if (result.size() > 0)
			return (result.get(0)).intValue();
		else
			return 0;
	}

	String UPDATE_SQL = " UPDATE promotions SET userId=? ,title=? ,content=?  WHERE id=? ";

	public int updatePromotions(final Promotions bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(UPDATE_SQL,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	throws SQLException {
							int i = 1;
							ps.setInt(i++, bean.getUserId());
							ps.setString(i++, bean.getTitle());
							ps.setString(i++, bean.getContent());
							ps.setInt(i++, bean.getId());
						}
					});
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}

	String INSERT_SQL = " INSERT INTO promotions (userId,title,content ) VALUES( ?,?,? ) ";
	public int insertPromotions(final Promotions bean) {
		int rtv = 0;
		try {
			rtv = jdbcTemplate.update(INSERT_SQL,
					new PreparedStatementSetter() {
						public void setValues(PreparedStatement ps)	 throws SQLException {
							int i = 1;
							ps.setInt(i++, bean.getUserId());
							ps.setString(i++, bean.getTitle());
							ps.setString(i++, bean.getContent());
						}
					});
		} catch (Exception e) {
			e.printStackTrace();
		}
		return rtv;
	}

	String GET_BYID_SQL = " SELECT * FROM  promotions  WHERE id=? ";
	public List<Promotions> getPromotionsById(final int keyValue) {
		final List<Promotions> result = new ArrayList<Promotions>();
		try {
			jdbcTemplate.query(GET_BYID_SQL, new PreparedStatementSetter() {
				public void setValues(PreparedStatement ps) throws SQLException {
					ps.setInt(1, keyValue);
				}
			}, new RowCallbackHandler() {
				public void processRow(ResultSet rs) throws SQLException {
					Promotions to = new Promotions();
					to.setId(rs.getInt("id"));
					to.setUserId(rs.getInt("userId"));
					to.setTitle(rs.getString("title"));
					to.setContent(rs.getString("content"));
					to.setCreateTime(rs.getTimestamp("createTime"));
					result.add(to);
				}
			});
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}
	

	String GET_ALL_SQL = "  SELECT * FROM  promotions  ORDER BY id ";

	public List<Promotions> getAllPromotions(final int beginRecord) {
		final List<Promotions> result = new ArrayList<Promotions>();
		String sql = GET_ALL_SQL + " LIMIT " + beginRecord + ", "
				+ (beginRecord + Constant.PAGE_SIZE);
		try {
			jdbcTemplate.query(sql, new RowCallbackHandler() {
				public void processRow(ResultSet rs) throws SQLException {
					Promotions to = new Promotions();
					to.setId(rs.getInt("id"));
					to.setUserId(rs.getInt("userId"));
					to.setTitle(rs.getString("title"));
//					to.setContent(rs.getString("content"));
					to.setCreateTime(rs.getTimestamp("createTime"));
					result.add(to);
				}
			});
		} catch (Exception e) {
			e.printStackTrace();
		}
		return result;
	}

}
